Power BI embed to Dynamics 365 form with filtering

There have been several post about this topic before, but this one is the only way I got it to work. Rasmus wrote about this a few weeks ago, and I am using most of his code. Ida was also early out mentioning this possibility, but I just couldn’t get it all to work before now. Big thank you to Jeroen Vos for BI assistance:)

Bear in mind that I had to start from scratch here to make sense of it all. You can fast forward quite a bit if you already have a data model. I believe Microsoft will release a OOTB function sometime this year, but until then we can demo embedded BI in CRM forms like this.

Create a report

Start by opening Power BI dashboard, and creating a new project with “Get Data”

If you don’t know where to find the developer settings, you can copy the url from above, and just replace it with your org and crm* that you have in your country.

For this demo I am only choosing Account and Opportunity.

After loading, add the funnel graph.

Add the field salesstage and estimtedvalue. Both are from Oppty table.

• 

• 

Your report should resemble something like this. Now we have to format quite a bit for this to actually look presentable in Dynamics. Because this solution is highly custom at the moment, we need to make it fit the Dynamics IFrame for it to look decent.

The most important format of them all is the page format to the right. You need to make this one small for the Dynamics Tab to be able to open it in full screen. I made it half of the standard. Eventually you should end up like something below:

Publish to powerbi.com

Remember to copy the URL here

Dynamics 365 form configuration

This process requires a webresource with javascript and a tab with IFrame in Dynamics. Because of the new UI this layout is actually quite nice!

function SetBIFrame(executionContext){
    var formContext = executionContext.getFormContext();
    var pbiFrame = formContext.getControl("IFRAME_BI");
    var GUID = formContext.data.entity.getId().replace(/[{}]/g, "");
    //replace the Power BI url with the url from the Embed step
    var PBIurl = "https://app.powerbi.com/reportEmbed?reportId=d86fdc2b-316a-420e-b177-9e477c879031&autoAuth=true&ctid=83121429-cfda-4a1b-89c9-969eb42dc605";
    //accounts = account table in BI, and accountid=guid for account
    pbiFrame.setSrc(PBIurl + "&filter=accounts/accountid eq '" +GUID+ "'");
}

Only thing you need to change is the URL from the powerbi.com secure embed. Since I am using account and opportunity out of the box, the table names for both of them are “accounts” and “opportunities”.

In the filter above I am filtering the results in the Account table with the account ID that i will find within CRM. The result is to show a graph for only that single account.

Remember to Pass Execution Context here. I don’t really now why, but it wont work without. Normally when I script I don’t have to use this, but if someone would tell me what it does I am happy to learn:)

Publish all settings and open Dyanmics 365 to the account (that you know has some opportunities).

As you can see Alpine Ski House is showing a sales funnel of the opportunities related and nothing more. This is a filtered report in Dynamics based on the account in a BI tab on top. Navigation here in dynamics is pretty smooth.